package com.tvb.infoapp.database;

import java.util.ArrayList;
import java.util.List;

import com.tvb.infoapp.object.CLSB_Field;
import com.tvb.infoapp.object.CLSB_Record;
import com.tvb.infoapp.object.CLSB_Structure;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseHelper extends SQLiteOpenHelper {
    
    // Table clsb_record
    
    private static final String TABLE_RECORD = "clsb_record";
    private static final String KEY_RECORD_ID = "id";
    private static final String KEY_RECORD_STRUCTURE_ID = "structure_id";
    private static final String KEY_RECORD_TITLE = "title";
    private static final String KEY_RECORD_TYPE = "type";
    private static final String KEY_RECORD_PATH = "path";
    private static final String KEY_RECORD_DES = "des";
    private static final String KEY_RECORD_CONTENT = "content";
    private static final String KEY_RECORD_KEYWORD = "keyword";
    
    //Table clsb_structure
    
    private static final String TABLE_STRUCTURE = "clsb_structure";
    private static final String KEY_STRUCTURE_ID = "id";
    private static final String KEY_STRUCTURE_NAME = "name";
    private static final String KEY_STRUCTURE_DES = "des";
    
    //Table clsb_field_ftructure
    private static final String TABLE_FIELD_STRUCTURE = "clsb_field_ftructure";
    private static final String KEY_FIELD_STRUCTURE_ID = "id";
    private static final String KEY_FIELD_STRUCTURE_STRUCTURE_ID = "structure_id";
    private static final String KEY_FIELD_STRUCTURE_FIELD_NAME = "field_name";
    private static final String KEY_FIELD_STRUCTURE_FIELD_TYPE = "field_type";
    private static final String KEY_FIELD_STRUCTURE_SEARCHABLE = "searchable";
    private static final String KEY_FIELD_STRUCTURE_SORTABLE = "sortable";
    
    //Table clsb_record_field
    
    private static final String TABLE_RECORD_FIELD = "clsb_record_field";
    private static final String KEY_RECORD_FIELD_RECORD_ID = "record_id";
    private static final String KEY_RECORD_FIELD_FIELD_ID = "field_id";
    private static final String KEY_RECORD_FIELD_VALUE = "value";
    
    //Table clsb_record_relation
    
    private static final String TABLE_RECORD_RELATION = "clsb_record_relation";
    private static final String KEY_RECORD_RELATION_RECORD_ID = "record_id";
    private static final String KEY_RECORD_RELATION_MAP_ID = "map_id";

//    private Context mContext;

    public DatabaseHelper(Context context) {
        super(context, "BookDatabase", null, 1);
        // TODO Auto-generated constructor stub
//        mContext = context;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub
        //Creat table clsb_record
        String CREAT_RECORD = "CREATE TABLE " + TABLE_RECORD + "("
                + KEY_RECORD_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + KEY_RECORD_STRUCTURE_ID + " INTEGER,"
                + KEY_RECORD_TITLE + " TEXT,"
                + KEY_RECORD_TYPE + " TEXT,"
                + KEY_RECORD_PATH + " TEXT,"
                + KEY_RECORD_DES + " TEXT,"
                + KEY_RECORD_CONTENT + " TEXT,"
                + KEY_RECORD_KEYWORD + " TEXT)";
        db.execSQL(CREAT_RECORD);
        
        // Creat Table clsb_structure
        String CREAT_STRUCTURE = "CREATE TABLE " + TABLE_STRUCTURE + "("
                + KEY_STRUCTURE_ID  + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + KEY_STRUCTURE_NAME + " TEXT,"
                + KEY_STRUCTURE_DES + " TEXT )";
        db.execSQL(CREAT_STRUCTURE);
        
        // Creat table clsb_field_structure
        
        String CREAT_FIELD_STRUCTURE = "CREATE TABLE " + TABLE_FIELD_STRUCTURE + "("
                + KEY_FIELD_STRUCTURE_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + KEY_FIELD_STRUCTURE_STRUCTURE_ID + " INTEGER,"
                + KEY_FIELD_STRUCTURE_FIELD_NAME + " TEXT,"
                + KEY_FIELD_STRUCTURE_FIELD_TYPE + " TEXT,"
                + KEY_FIELD_STRUCTURE_SEARCHABLE + " INTEGER,"
                + KEY_FIELD_STRUCTURE_SORTABLE + " INTEGER )";
        db.execSQL(CREAT_FIELD_STRUCTURE);
        
        // Creat table clsb_record_field
        
        String CREAT_RECORD_FIELD = "CREATE TABLE " + TABLE_RECORD_FIELD + "("
                + KEY_RECORD_FIELD_RECORD_ID + " INTEGER,"
                + KEY_RECORD_FIELD_FIELD_ID + " INTEGER,"
                + KEY_RECORD_FIELD_VALUE + " TEXT)";
        db.execSQL(CREAT_RECORD_FIELD);
        
        // Creat table clsb_record_relation
        String CREAT_RECORD_RELATION = "CREATE TABLE " + TABLE_RECORD_RELATION + "("
                + KEY_RECORD_RELATION_RECORD_ID + " INTEGER,"
                + KEY_RECORD_RELATION_MAP_ID + " INTEGER)";
        db.execSQL(CREAT_RECORD_RELATION);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_RECORD);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_STRUCTURE);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_FIELD_STRUCTURE);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_RECORD_FIELD);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_RECORD_RELATION);
    }
    // insert record
    public void insertRecord (CLSB_Record record) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(KEY_RECORD_STRUCTURE_ID, record.getStructure_id());
        values.put(KEY_RECORD_TITLE, record.getTitle());
        values.put(KEY_RECORD_TYPE, record.getType());
        values.put(KEY_RECORD_PATH, record.getPath());
        values.put(KEY_RECORD_DES, record.getDes());
        values.put(KEY_RECORD_CONTENT, record.getContent());
        values.put(KEY_RECORD_KEYWORD, record.getKey_word());
        db.insert(TABLE_RECORD, null, values);
        db.close();
    }
    //insert structure
    public void insertStructure (CLSB_Structure structure) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(KEY_STRUCTURE_NAME, structure.getName());
        values.put(KEY_STRUCTURE_DES, structure.getDes());
        db.insert(TABLE_STRUCTURE, null, values);
        db.close();
    }
    // insert field
    public void insertField (CLSB_Field field) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(KEY_FIELD_STRUCTURE_STRUCTURE_ID, field.getStructure_id());
        values.put(KEY_FIELD_STRUCTURE_FIELD_NAME, field.getName());
        values.put(KEY_FIELD_STRUCTURE_FIELD_TYPE, field.getType());
        values.put(KEY_FIELD_STRUCTURE_SEARCHABLE, field.getSearchable());
        values.put(KEY_FIELD_STRUCTURE_SORTABLE, field.getSortable());
        db.insert(TABLE_FIELD_STRUCTURE, null, values);
        db.close();
    }
    // insert record_field
    public void insertRecordField (int record_id, int field_id, String value) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(KEY_RECORD_FIELD_RECORD_ID, record_id);
        values.put(KEY_RECORD_FIELD_FIELD_ID, field_id);
        values.put(KEY_RECORD_FIELD_VALUE, value);
        db.insert(TABLE_RECORD_FIELD, null, values);
        db.close();
    }
    
    // get all structure
    
    public List<CLSB_Structure> getAllStructure() {
        List<CLSB_Structure> list_structure = new ArrayList<CLSB_Structure>();
        SQLiteDatabase db = this.getReadableDatabase();
        String query = "SELECT * FROM " + TABLE_STRUCTURE;
        Cursor cursor = db.rawQuery(query, null);
        if (cursor.moveToFirst()) {
            do {
                list_structure.add(new CLSB_Structure(cursor.getInt(0),
                        cursor.getString(1), cursor.getString(2)));
            }while(cursor.moveToNext());
        }
        return list_structure;
    }
    
    public List<CLSB_Field> getListFieldByStructure (int structure_id){
        List<CLSB_Field> list_field = new ArrayList<CLSB_Field>();
        SQLiteDatabase db = this.getReadableDatabase();
        String query = "SELECT * FROM " + TABLE_FIELD_STRUCTURE
                + " WHERE " + KEY_FIELD_STRUCTURE_STRUCTURE_ID + "=" + structure_id;
        Cursor cursor = db.rawQuery(query, null);
        if (cursor.moveToFirst()) {
            do {
                list_field.add(new CLSB_Field(cursor.getInt(0), cursor.getInt(1),
                        cursor.getString(2), cursor.getString(3), cursor.getInt(4), cursor.getInt(5)));
            }while(cursor.moveToNext());
        }
        return list_field;
    }
    
    public List<CLSB_Record> getAllRecord(){
        List<CLSB_Record> list_record = new ArrayList<CLSB_Record>();
        SQLiteDatabase db = this.getReadableDatabase();
        String query = "SELECT * FROM " + TABLE_RECORD;
        Cursor cursor = db.rawQuery(query, null);
        if (cursor.moveToFirst()) {
            do {
               list_record.add(new CLSB_Record(cursor.getInt(0),
                       cursor.getInt(1), cursor.getString(2), cursor.getString(3),
                       cursor.getString(4), cursor.getString(5), 
                       cursor.getString(6), cursor.getString(7)));
            }while(cursor.moveToNext());
        }
        return list_record;
    }
    
    public boolean checkRecordDataBase(){
        boolean result = false;
        SQLiteDatabase db = this.getReadableDatabase();
        String query = "SELECT * FROM " + TABLE_RECORD;
        Cursor cursor = db.rawQuery(query, null);
        if (cursor.getCount() > 0) {
            result = true;
        } else {
            result = false;
        }
        db.close();
        return result;
    }
}
